import glob
import pandas as pd
import re

#列出所有值班表路径（在此处自定义你要处理的文件夹）
SheetPaths = glob.glob('故障值班/*.xlsx')

#先打开文件夹中第一个表格
df = pd.read_excel(SheetPaths[0], engine='openpyxl', header=None)

#循环且纵向拼接文件夹中的表格
for i in range(1, len(SheetPaths)):
    df = pd.concat([df, pd.read_excel(SheetPaths[i], engine='openpyxl', header=None)], axis=0)
    
#去除包含“班表名称”、“班次时间”、“日期”的行，删除空行
df = df[df[0].str.contains('班表名称|班次时间|日期') == False]

#重置索引
df.index = range(df.shape[0])

#重命名列
df.rename(columns={0:"时间", 1:"值班A", 2:"值班B"}, inplace=True)

#找出包含“部门”的行索引
idx = list(df[df['时间'].str.contains('部 门') == True].index)

#idx列表末尾插入表格大小
idx.append(df.shape[0])

#插入【部门】列
df.insert(0, '部门', '')

#对【部门】列进行分段填充
for i in range(1, len(idx)):
    df.loc[idx[i-1]+1:idx[i]-1, '部门'] = df['时间'][idx[i-1]].split('/')[-2]
    
#去除包含“部 门”的行
df = df[df['时间'].str.contains('部 门') == False]

#从【时间】列切分出年—月
df['时间'] = df['时间'].apply(lambda x:'-'.join(x.split('-')[0:2]))

#对【值班A】列和【值班B】列中的名字去重
df['值班A'] = df['值班A'].apply(lambda x:','.join(list(set(x.replace('计划：', '').replace('\n', '').replace('实际：', '').replace('*', '').split(',')))))
df['值班B'] = df['值班B'].apply(lambda x:','.join(list(set(x.replace('计划：', '').replace('\n', '').replace('实际：', '').replace('*', '').split(',')))))

#合并【值班A】和【值班B】列
df.insert(0, '值班', df['值班A']+','+df['值班B'])

#去除【值班】列中的杂质
df['值班'] = df['值班'].apply(lambda x:x.replace('中通', '').replace('省工程', ''))

#将一行拆分成多行
df = df.drop(['值班'], axis=1).join(df['值班'].str.split(',', expand=True).stack().reset_index(level=1, drop=True).rename('姓名'))

#插入【值班角】列
df.insert(0, '值班角', '')
df['值班角'] = df['姓名'].apply(lambda x:re.findall(r'[A,B]角', x))

#将【姓名】列中的A角，B角去除
df['姓名'] = df['姓名'].apply(lambda x:x.split('(')[0])

#将【值班角】列转为str
df['值班角'] = df['值班角'].apply(lambda x:str(x).strip("[]''"))

#插入【值班次数】列
df.insert(0, '值班次数', 1)

#插入【值班时长(小时)】列
df.insert(0, '值班时长(小时)', 4)

#插入【时间+部门】列
df.insert(0, '时间+部门', df['时间']+df['部门'])

#计算各月各公司的值班总时长
df_time = df.groupby(['时间', '部门']).sum(numeric_only=True).reset_index()
df_time.insert(0, '时间+部门', df_time['时间']+df_time['部门'])
df_time = df_time[['时间+部门', '值班时长(小时)']]
df_time.rename(columns={'值班时长(小时)' : '值班总时长(小时)'}, inplace=True)

#连接df和df_time
df = pd.merge(df, df_time)

#插入【时长占比】列并计算时长占比
df.insert(0, '时长占比(%)', df['值班时长(小时)']/df['值班总时长(小时)']*100)

#重新对列排序并去除不要的列
df = df[['时间', '部门', '姓名', '值班角', '值班次数', '值班时长(小时)', '时长占比(%)']]

#生成统计表
df_stat = df.groupby(['时间', '部门', '姓名']).sum(numeric_only=True)

#输出报表
with pd.ExcelWriter('故障值班统计.xlsx') as writer:
    df.to_excel(writer, sheet_name='清单', index=False)
    df_stat.to_excel(writer, sheet_name='统计')